package cn.edu.sdjzu.xg.eagle.dao;

import cn.edu.sdjzu.xg.eagle.Service.DepartmentDaoService;
import cn.edu.sdjzu.xg.eagle.Service.SchoolDaoService;
import cn.edu.sdjzu.xg.eagle.entity.Department;
import cn.edu.sdjzu.xg.eagle.entity.School;
import cn.edu.sdjzu.xg.eagle.exception.BaseException;
import cn.edu.sdjzu.xg.eagle.factory.DaoFactory;
import cn.edu.sdjzu.xg.eagle.util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;

public class DepartmentDaoImpl implements DepartmentDaoService {

    //单例模式
    private static DepartmentDaoImpl departmentDaoImpl = new DepartmentDaoImpl();

    private DepartmentDaoImpl() {}

    public static DepartmentDaoImpl getInstance() {return departmentDaoImpl; }

    @Override
    public Department findDepartment(Long id) throws SQLException, BaseException {
        Department department = null;
        Connection connection = JdbcHelper.getConn();
        String sql = "SELECT * FROM department WHERE id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setLong(1, id);
        ResultSet rs = ps.executeQuery();
        try {
            if (rs.next()) {
                String description = rs.getString("description");
                String no = rs.getString("no");
                String remarks = rs.getString("remarks");
                Long school_id = rs.getLong("school_id");
                SchoolDaoService schoolDao = SchoolDaoImpl.getInstance();
                School school = schoolDao.findSchool(school_id);
                department = new Department(id, description, no, remarks, school);
            }
        } finally {
            JdbcHelper.close(connection);
        }
        return department;
    }

    @Override
    public Collection<Department> findDepartments() throws SQLException, BaseException {
        Collection<Department> departments = new HashSet<>();
        Connection connection = JdbcHelper.getConn();
        String sql = "SELECT * FROM department";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        try {
            while (rs.next()) {
                Long id = rs.getLong("id");
                String description = rs.getString("description");
                String no = rs.getString("no");
                String remarks = rs.getString("remarks");
                Long school_id = rs.getLong("school_id");
                SchoolDaoService schoolDao = SchoolDaoImpl.getInstance();
                School school = schoolDao.findSchool(school_id);
                departments.add(new Department(id, description, no, remarks, school));
            }
        } finally {
            JdbcHelper.close(connection); // 此方法独立管理连接
        }
        return departments;
    }

    @Override
    public boolean addDepartment(Connection connection, Department department) throws SQLException, BaseException {
        String sql = "INSERT INTO department(description,no,remarks,school_id) VALUES(?,?,?,?)";
        try (PreparedStatement pstmt = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS)) {
            pstmt.setString(1, department.getDescription());
            pstmt.setString(2, department.getNo());
            pstmt.setString(3, department.getRemarks());
            pstmt.setLong(4, department.getSchool().getId());
            int rowAffected = pstmt.executeUpdate(); // 只保留一次执行

            if (rowAffected > 0) {
                try (ResultSet rs = pstmt.getGeneratedKeys()) {
                    if (rs.next()) {
                        department.setId(rs.getLong(1)); // 正确设置 id
                    }
                }
            }
            return rowAffected > 0;
        }
    }

    @Override
    public boolean updateDepartment(Connection connection, Department department) throws SQLException, BaseException {
        String sql = "UPDATE department SET description = ?,no = ?,remarks = ?,school_id = ? WHERE id = ?";
        try(PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, department.getDescription());
            pstmt.setString(2, department.getNo());
            pstmt.setString(3, department.getRemarks());
            pstmt.setLong(4, department.getSchool().getId());
            pstmt.setLong(5, department.getId());
            return pstmt.executeUpdate() > 0;
        }
    }

    @Override
    public boolean deleteDepartment(Integer id) throws SQLException, BaseException {
        String sql = "DELETE FROM department WHERE id = ?";
        Connection connection = JdbcHelper.getConn();
        try (PreparedStatement pstmt = JdbcHelper.getConn().prepareStatement(sql)) {
            pstmt.setInt(1, id);
            return pstmt.executeUpdate() > 0;
        }finally {
            JdbcHelper.close(connection);
        }
    }
}